#pragma once

#include "SQLite3Wrapper.hpp"
#include "config.h"
#include "debug.hpp"
#include "driver.hpp"
#include "park.hpp"
#include <boost/uuid/uuid.hpp>            // uuid class
#include <boost/uuid/uuid_generators.hpp> // generators
#include <boost/uuid/uuid_io.hpp>
#include <filesystem>
#include <fmt/format.h>
#include <nlohmann/json.hpp>
#include <vector>

struct ParkDB {
    using uuid = boost::uuids::uuid;

private:
    SQLite3Wrapper db;
    ParkDB(const std::string &path) : db(path) {};
    ParkDB(const ParkDB &) = delete;

public:
    ~ParkDB() = default;
    inline static ParkDB *instance = nullptr;
    static ParkDB *getInstance() {
        if (instance == nullptr) {
            // 如果文件不存在，从init.sql文件中初始化
            if (!std::filesystem::exists(DB_FILE)) {
                instance = new ParkDB(DB_FILE);
                debug(), "db " DB_FILE " not found, initializing from init.sql";
                instance->db.initializeFromFile(INIT_SQL_FILE);
            } else {
                instance = new ParkDB(DB_FILE);
                debug(), "db " DB_FILE " found";
            }
        }
        return instance;
    }

    /**
     * @brief Get the All Cars object
     *
     * @return nlohmann::json
     * CREATE TABLE IF NOT EXISTS car (
        license_plate_number varchar(64) PRIMARY KEY,
        curr_status INTEGER NOT NULL,
        enter_time TIMESTAMP,
        exit_time TIMESTAMP,
        enter_driver varchar(64),
        exit_driver varchar(64)
    );
     */
    nlohmann::json getAllInfo() {
        nlohmann::json result;
        try {
            db.queryWithCallback(
                "SELECT license_plate, curr_status, enter_time, "
                "exit_time, enter_driver, exit_driver, spot_id, id, cost FROM "
                "parkInfo",
                [&result](const std::vector<std::string> &row) -> bool {
                    result.push_back({{"license_plate", row[0]},
                                      {"curr_status", row[1]},
                                      {"enter_time", row[2]},
                                      {"exit_time", row[3]},
                                      {"enter_driver", row[4]},
                                      {"exit_driver", row[5]},
                                      {"spot_id", row[6]},
                                      {"id", row[7]},
                                      {"cost", row[8]}});
                    return true;
                });
            return result;
        } catch (const std::exception &e) {
            debug(), "error when getting all cars: ", e.what();
            throw;
        }
    }

    /**
     * @brief 根据查询条件查询车辆信息
     *
     * @param plate 车牌号
     * @param status 状态
     * @param enter_driver 入口设备号
     * @param enter_time 入场时间 (查询小于该时间的)
     * @param exit_time 出场时间 （查询小于该时间的）
     * @return nlohmann::json
     */
    nlohmann::json getInfo(std::string_view id, std::string_view plate = {},
                           int status = -1, std::string_view spot_id = {},
                           std::string_view enter_driver = {},
                           std::time_t enter_time = 0,
                           std::string_view exit_driver = {},
                           std::time_t exit_time = 0) {
        nlohmann::json result;
        std::string query =
            "SELECT license_plate, curr_status, enter_time, exit_time, "
            "enter_driver, exit_driver, spot_id, id, cost FROM parkInfo WHERE "
            "1=1";
        if (!id.empty()) {
            query += fmt::format(" AND id = '{}'", id);
        }
        if (!plate.empty()) {
            query += fmt::format(" AND license_plate = '{}'", plate);
        }
        if (status != -1) {
            query += fmt::format(" AND curr_status = '{}'", status);
        }
        if (!spot_id.empty()) {
            query += fmt::format(" AND spot_id = '{}'", spot_id);
        }
        if (!enter_driver.empty()) {
            query += fmt::format(" AND enter_driver = '{}'", enter_driver);
        }
        if (enter_time != 0) {
            query += fmt::format(" AND enter_time < '{}'", enter_time);
        }
        if (!exit_driver.empty()) {
            query += fmt::format(" AND exit_driver = '{}'", exit_driver);
        }
        if (exit_time != 0) {
            query += fmt::format(" AND exit_time < '{}'", exit_time);
        }
        debug(), "getInfo query: ", query;
        try {
            db.queryWithCallback(
                query, [&result](const std::vector<std::string> &row) -> bool {
                    result.push_back(
                        {{"license_plate", row[0]},
                         {"curr_status", std::stoi(row[1])},
                         {"enter_time", row[2] == "" ? 0 : stoi(row[2])},
                         {"exit_time", row[3] == "" ? 0 : stoi(row[3])},
                         {"enter_driver", row[4]},
                         {"exit_driver", row[5]},
                         {"spot_id", row[6]},
                         {"id", row[7]},
                         {"cost", row[8] == "" ? 0 : stoi(row[8])}});
                    return true;
                });
            return result;
        } catch (const std::exception &e) {
            debug(), "error when getting info: ", e.what();
            throw;
        }
    }

    void insertInfo(ParkInfo info) const {
        auto query = fmt::format(
            "INSERT INTO parkInfo (id, license_plate, curr_status, "
            "enter_time, exit_time, enter_driver, exit_driver, spot_id) "
            "VALUES ('{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}')",
            info.id, info.license_plate, uint8_t(info.curr_status),
            info.enter_time, info.exit_time, info.enter_driver,
            info.exit_driver, info.spot_id);
        try {
            debug(), "insert query: ", query;
            db.execute(query);
        } catch (const std::exception &e) {
            debug(), "error when inserting car: ", e.what();
            throw;
        }
    }

    void updateInfo(ParkInfo info) const {
        auto query = fmt::format(
            "UPDATE parkInfo SET curr_status = '{}', exit_time = '{}', "
            "exit_driver = '{}', license_plate = '{}', cost = '{}' WHERE id = "
            "'{}'",
            uint8_t(info.curr_status), info.exit_time, info.exit_driver,
            info.license_plate, info.cost, info.id);
        try {
            db.execute(query);
        } catch (const std::exception &e) {
            debug(), "error when updating car: ", e.what();
            throw;
        }
    }

    /**
     * @brief Get the All Cars object
     *
     * @return nlohmann::json
     * CREATE TABLE IF NOT EXISTS driver (
        id INTEGER PRIMARY KEY,
        driver_name varchar(64) NOT NULL,
        driver_type INTEGER NOT NULL
    );
     */
    nlohmann::json getAllDrivers() {
        nlohmann::json result;
        try {
            db.queryWithCallback(
                "SELECT id, driver_name, driver_type FROM driver",
                [&result](const std::vector<std::string> &row) -> bool {
                    result.push_back({{"id", row[0]},
                                      {"driver_name", row[1]},
                                      {"driver_type",
                                       row[2] == "" ? 0 : std::stoi(row[2])}});
                    return true;
                });
            return result;
        } catch (const std::exception &e) {
            debug(), "error when getting all drivers: ", e.what();
            throw;
        }
    }

    void insertDriver(Driver driver) const {
        auto query = fmt::format("INSERT INTO driver (id, driver_name, "
                                 "driver_type) "
                                 "VALUES ('{}', '{}', '{}')",
                                 driver.id, driver.driver_name,
                                 uint8_t(driver.driver_type));
        try {
            debug(), "insertDriver query: ", query;
            db.execute(query);
        } catch (...) {
            debug(), "error when inserting driver: ", driver.id;
            throw;
        }
    }

    /**
     * @brief Get the Drivers by query
     *
     * @param driver_id
     * @return nlohmann::json
     */
    nlohmann::json getDrivers(std::string_view driver_id = {},
                              std::string_view driver_name = {},
                              uint8_t driver_type = {}) {
        nlohmann::json result;
        std::string query =
            "SELECT id, driver_name, driver_type FROM driver WHERE 1=1";
        if (!driver_id.empty()) {
            query += fmt::format(" AND id = '{}'", driver_id);
        }
        if (!driver_name.empty()) {
            query += fmt::format(" AND driver_name = '{}'", driver_name);
        }
        if (driver_type != -1) {
            query += fmt::format(" AND driver_type = '{}'", driver_type);
        }
        try {
            db.queryWithCallback(
                query, [&result](const std::vector<std::string> &row) -> bool {
                    result.push_back({{"id", row[0]},
                                      {"driver_name", row[1]},
                                      {"driver_type", row[2]}});
                    return true;
                });
            return result;
        } catch (const std::exception &e) {
            debug(), "error when getting driver by id: ", e.what();
            throw;
        }
    }

    /**
     * @brief Get the All Spot object
     *
     * @return nlohmann::json
     *
     * CREATE TABLE IF NOT EXISTS spot (
        id INTEGER PRIMARY KEY,
        curr_status INTEGER NOT NULL,
        curr_car_plate varchar(64)
    );
     */
    nlohmann::json getAllSpot() {
        nlohmann::json result;
        try {
            db.queryWithCallback(
                "SELECT id, curr_status, info_id FROM "
                "spot",
                [&result](const std::vector<std::string> &row) -> bool {
                    result.push_back({{"id", row[0]},
                                      {"curr_status", row[1]},
                                      {"info_id", row[2]}});
                    return true;
                });
            return result;
        } catch (const std::exception &e) {
            debug(), "error when getting all spaces: ", e.what();
            throw;
        }
    }

    void insertSpot(Spot spot) const {
        auto query =
            fmt::format("INSERT INTO spot (id, curr_status, "
                        "info_id) "
                        "VALUES ('{}', '{}', '{}')",
                        spot.id, uint8_t(spot.curr_status), spot.info_id);
        try {
            db.execute(query);
        } catch (const std::exception &e) {
            debug(), "error when updating space: ", e.what();
            throw;
        }
    }

    void updateSpot(Spot spot) const {
        auto query =
            fmt::format("UPDATE spot SET curr_status = '{}', "
                        "info_id = '{}' WHERE id = '{}'",
                        uint8_t(spot.curr_status), spot.info_id, spot.id);
        try {
            db.execute(query);
        } catch (const std::exception &e) {
            debug(), "error when updating space: ", e.what();
            throw;
        }
    }

    /**
     * @brief Get the Spaces By query
     *
     * @param id
     * @return nlohmann::json
     */
    nlohmann::json getSpot(std::string_view id = {}, int status = -1,
                           std::string_view info_id = {}) {
        nlohmann::json result;
        std::string query = "SELECT id, curr_status, info_id FROM "
                            "spot WHERE 1=1";
        if (!id.empty()) {
            query += fmt::format(" AND id = '{}'", id);
        }
        if (status != -1) {
            query += fmt::format(" AND curr_status = '{}'", status);
        }
        if (!info_id.empty()) {
            query += fmt::format(" AND curr_car_plate = '{}'", info_id);
        }
        debug(), "query: ", query;
        try {
            db.queryWithCallback(
                query, [&result](const std::vector<std::string> &row) -> bool {
                    result.push_back({{"id", row[0]},
                                      {"curr_status", stoi(row[1])},
                                      {"info_id", row[2]}});
                    return true;
                });
            return result;
        } catch (const std::exception &e) {
            debug(), "error when getting space: ", e.what();
            throw;
        }
    }

    /**
     * @brief Get the Spaces By query
     *
     * @param id
     * @return nlohmann::json
     */
    nlohmann::json getSpotWithInfo(std::string_view id = {}, int status = -1,
                                   std::string_view car_plate = {}) {
        nlohmann::json result;
        std::string query = "SELECT "
                            "spot.id AS spot_id,"
                            "spot.curr_status AS spot_status,"
                            "parkInfo.license_plate AS license_plate,"
                            "parkInfo.id AS park_id,"
                            "parkInfo.curr_status AS status,"
                            "parkInfo.enter_time AS enter_time,"
                            "parkInfo.exit_time AS exit_time,"
                            "parkInfo.enter_driver AS enter_driver,"
                            "parkInfo.exit_driver AS exit_driver, "
                            "parkInfo.cost AS cost FROM spot LEFT"
                            " JOIN parkInfo ON spot.info_id ="
                            "parkInfo.id WHERE 1=1";
        if (!id.empty()) {
            query += fmt::format(" AND spot.id = '{}'", id);
        }
        if (status != -1) {
            query += fmt::format(" AND spot.curr_status = {}", status);
        }
        if (!car_plate.empty()) {
            query +=
                fmt::format(" AND parkInfo.license_plate = '{}'", car_plate);
        }
        try {
            db.queryWithCallback(
                query, [&result](const std::vector<std::string> &row) -> bool {
                    result.push_back(
                        {{"spot_id", row[0]},
                         {"status", stoi(row[1])},
                         {"license_plate", row[2]},
                         {"info_id", row[3]},
                         {"car_status", row[4] == "" ? -1 : stoi(row[4])},
                         {"enter_time", row[5] == "" ? 0 : stoi(row[5])},
                         {"exit_time", row[6] == "" ? 0 : stoi(row[6])},
                         {"enter_driver", row[7]},
                         {"exit_driver", row[8]},
                         {"cost", row[9] == "" ? -1 : stoi(row[9])}});
                    return true;
                });
            return result;
        } catch (const std::exception &e) {
            debug(), "error when getting space: ", e.what();
            throw;
        }
    }
};
